const express = require('express');
const router = express.Router();
const db = require('../../../MySql');
const { formatDateTime } = require('../../utils/dateUtils')
const { generate19DigitId } = require('../../utils/idUtils')
const el_exam = require('../../models/el_exam')
const el_exam_repo = require('../../models/el_exam_repo')
const el_paper = require('../../models/el_paper')
const el_exam_depart = require('../../models/el_exam_depart')
const sys_user = require('../../models/sys_user')

router.post('/exam/exam/online-paging', async (req, res) => {
    try {
        // 1. 参数验证和提取
        const { userId, openType, title } = req.body.params || {}
        const { current = 1, size = 10 } = req.body || {}

        let results = await el_exam.findExamAll(current, size)

        const user = await sys_user.findUserById(userId)

        const examIds = await el_exam_depart.findExamDepartByDepartId(user.depart_id)

        const examIdList = examIds.map(item => item.exam_id)

        // 2. 过滤：只保留指定部门的考试
        results = results.map(item => ({
            "id": item.id,
            "title": item.title,
            "content": item.content,
            "openType": item.open_type,
            "state": item.state,
            "timeLimit": item.time_limit,
            "startTime": formatDateTime(item.start_time) ? formatDateTime(item.start_time).substring(0, 10) : null,
            "endTime": formatDateTime(item.end_time) ? formatDateTime(item.end_time).substring(0, 10) : null,
            "createTime": formatDateTime(item.create_time),
            "updateTime": formatDateTime(item.update_time),
            "totalScore": item.total_score,
            "totalTime": item.total_time,
            "qualifyScore": item.qualify_score
        }))

        // 返回结果要去掉考试开始时间不是今天的
        // 获取今天的日期（考虑时区）
        const now = new Date()
        const today = formatDateTime(now).substring(0, 10)

        // 过滤：只保留开始时间不是今天的考试，同时排除startTime为null的记录    
        const startNotTodayRes = results.filter(item => item.startTime == today || item.startTime == null || (item.startTime <= today && item.endTime >= today))


        //指定部门考试
        const records = startNotTodayRes
            // .map(item => ({
            //     "id": item.id,
            //     "title": item.title,
            //     "content": item.content,
            //     "openType": item.open_type,
            //     "state": item.state,
            //     "timeLimit": item.time_limit,
            //     "startTime": formatDateTime(item.start_time) ? formatDateTime(item.start_time).substring(0, 10) : null,
            //     "endTime": formatDateTime(item.end_time) ? formatDateTime(item.end_time).substring(0, 10) : null,
            //     "createTime": formatDateTime(item.create_time),
            //     "updateTime": formatDateTime(item.update_time),
            //     "totalScore": item.total_score,
            //     "totalTime": item.total_time,
            //     "qualifyScore": item.qualify_score
            //}))
            .filter(item => examIdList.includes(item.id))

        //完全开发考试
        const records2 = startNotTodayRes
            // .map(item => ({
            //     "id": item.id,
            //     "title": item.title,
            //     "content": item.content,
            //     "openType": item.open_type,
            //     "state": item.state,
            //     "timeLimit": item.time_limit,
            //     "startTime": formatDateTime(item.start_time) ? formatDateTime(item.start_time).substring(0, 10) : null,
            //     "endTime": formatDateTime(item.end_time) ? formatDateTime(item.end_time).substring(0, 10) : null,
            //     "createTime": formatDateTime(item.create_time),
            //     "updateTime": formatDateTime(item.update_time),
            //     "totalScore": item.total_score,
            //     "totalTime": item.total_time,
            //     "qualifyScore": item.qualify_score
            // }))
            .filter(item => item.openType == 1)

        //合并指定部门考试和完全开发考试
        let records3 = [...records, ...records2]

        if (openType) {
            records3 = records3.filter(item => item.openType == openType)
        }
        if (title) {
            records3 = records3.filter(item => item.title.includes(title))
        }

        // 查询总数
        const countSql = `SELECT COUNT(*) as total FROM el_exam `
        const [countResult] = await db.query(countSql)
        const total = countResult[0].total

        return res.json({
            "msg": "操作成功！",
            "code": 0,
            "data": {
                "records": records3,
                "total": total,
                "size": size,
                "current": current,
                "orders": [],
                "optimizeCountSql": true,
                "hitCount": false,
                "searchCount": true,
                "pages": Math.ceil(total / size)
            },
            "success": true
        })
    } catch (err) {
        console.error(err)
        return res.json({
            msg: "数据库查询失败",
            code: 500,
            success: false
        })
    }
})


router.post('/exam/exam/paging', async (req, res) => {
    try {
        const { current, size } = req.body
        const { openType, title, startTime, endTime } = req.body.params

        const results = await el_exam.findExamAll(current, size)

        let records = results.map(item => ({
            "id": item.id,
            "title": item.title,
            "content": item.content,
            "openType": item.open_type,
            "state": item.state,
            "timeLimit": item.time_limit,
            "startTime": formatDateTime(item.start_time) ? formatDateTime(item.start_time).substring(0, 10) : null,
            "endTime": formatDateTime(item.end_time) ? formatDateTime(item.end_time).substring(0, 10) : null,
            "createTime": formatDateTime(item.create_time),
            "updateTime": formatDateTime(item.update_time),
            "totalScore": item.total_score,
            "totalTime": item.total_time,
            "qualifyScore": item.qualify_score

        }))

        // 根据params过滤
        if (startTime) {
            records = records.filter(item => item.startTime && item.startTime >= startTime)
        }
        if (endTime) {
            records = records.filter(item => item.endTime && item.endTime <= endTime)
        }
        if (openType) {
            records = records.filter(item => item.open_type == openType)
        }
        if (title) {
            records = records.filter(item => item.title.includes(title))
        }

        // 查询总数
        const countSql = `SELECT COUNT(*) as total FROM el_exam `
        const [countResult] = await db.query(countSql)
        const total = countResult[0].total

        return res.json({
            "msg": "操作成功！",
            "code": 0,
            "data": {
                "records": records,
                "total": total,
                "size": size,
                "current": current,
                "orders": [],
                "optimizeCountSql": true,
                "hitCount": false,
                "searchCount": true,
                "pages": 1
            },
            "success": true
        })
    } catch (err) {
        console.error(err)
        return res.json({
            msg: "数据库查询失败",
            code: 500,
            success: false
        })
    }

})


router.post('/exam/exam/detail', async (req, res) => {
    try {
        const { id } = req.body

        if (!id) {
            return res.json({ 
                "msg": "操作成功！", 
                "code": 0, 
                "data": { 
                    "repoList": [], 
                    "departIds": [] 
                }, 
                "success": true 
            })
        }

        // 1. 根据id查询考试详情
        const examResults = await el_exam.findExamById(id)
        if (!examResults) {
            return res.json({
                "msg": "考试不存在",
                "code": 404,
                "success": false
            })
        }

        // 2. 查询所有题库列表（用于前端选择）
        const [allRepos] = await db.query(`
            SELECT e.id, e.title, e.remark, e.create_time, e.update_time
            FROM el_repo e 
            ORDER BY e.create_time DESC
        `)

        // 3. 统计每个题库对应的题目数量
        const repoListWithCounts = await Promise.all(allRepos.map(async (repo) => {
            const [quTypeRes] = await db.query(`
                SELECT 
                    SUM(CASE WHEN qu_type = 1 THEN 1 ELSE 0 END) as radioCount,
                    SUM(CASE WHEN qu_type = 2 THEN 1 ELSE 0 END) as multiCount,
                    SUM(CASE WHEN qu_type = 3 THEN 1 ELSE 0 END) as judgeCount
                FROM el_qu_repo 
                WHERE repo_id = ?
            `, [repo.id])

            return {
                "id": repo.id,
                "code": "",
                "title": repo.title,
                "remark": repo.remark,
                "createTime": formatDateTime(repo.create_time),
                "updateTime": formatDateTime(repo.update_time),
                "radioCount": parseInt(quTypeRes[0]?.radioCount) || 0,
                "multiCount": parseInt(quTypeRes[0]?.multiCount) || 0,
                "judgeCount": parseInt(quTypeRes[0]?.judgeCount) || 0
            }
        }))

        // 4. 查询考试题库关联记录
        const repoResults = await el_exam_repo.findExamRepoByExamId(id)
        const repoList = repoResults.map(item => {
            // 查找题库详情
            const repoDetail = repoListWithCounts.find(repo => repo.id === item.repo_id)
            
            return {
                "id": item.id,
                "examId": item.exam_id,
                "repoId": item.repo_id,
                "radioCount": item.radio_count,
                "radioScore": item.radio_score,
                "multiCount": item.multi_count,
                "multiScore": item.multi_score,
                "judgeCount": item.judge_count,
                "judgeScore": item.judge_score,
                "totalRadio": repoDetail?.radioCount || 0,
                "totalMulti": repoDetail?.multiCount || 0,
                "totalJudge": repoDetail?.judgeCount || 0
            }
        })

        // 5. 查询考试部门关联
        const departIdsRes = await el_exam_depart.findExamDepartByExamId(id)
        const departIds = departIdsRes.map(item => item.depart_id)

        // 6. 计算考试状态（如果未设置状态）
        let state = examResults.state
        if (examResults.time_limit === 1 && examResults.start_time && examResults.end_time) {
            const now = new Date()
            const startTime = new Date(examResults.start_time)
            const endTime = new Date(examResults.end_time)
            
            if (now < startTime) {
                state = 2 // 未开始
            } else if (now >= startTime && now <= endTime) {
                state = 0 // 进行中
            } else if (now > endTime) {
                state = 3 // 已结束
            }
        }

        return res.json({
            "msg": "操作成功！",
            "code": 0,
            "data": {
                "id": examResults.id,
                "title": examResults.title,
                "content": examResults.content,
                "openType": examResults.open_type,
                "state": state, // 使用计算后的状态
                "timeLimit": examResults.time_limit == 1,
                "startTime": examResults.start_time ? examResults.start_time.substring(0, 10) : null,
                "endTime": examResults.end_time ? examResults.end_time.substring(0, 10) : null,
                "createTime": formatDateTime(examResults.create_time),
                "updateTime": formatDateTime(examResults.update_time),
                "totalScore": examResults.total_score,
                "totalTime": examResults.total_time,
                "qualifyScore": examResults.qualify_score,
                "repoList": repoList,
                "departIds": departIds,
            },
            "success": true
        });

    } catch (err) {
        console.error('考试详情查询失败:', err);
        return res.json({
            msg: "考试详情查询失败",
            code: 500,
            success: false
        })
    }
})


router.post('/exam/exam/save', async (req, res) => {
    try {
        const {
            id,
            title,
            content,
            openType,
            timeLimit,
            totalScore,
            totalTime,
            qualifyScore, startTime, endTime,
            repoList = [],
            departIds = []
        } = req.body

        let { state } = req.body

        const newId = generate19DigitId()

        if (timeLimit && !startTime) {
            return res.json({
                msg: "请选择考试时间！",
                code: 400,
                success: false
            })
        }
        // 如果有ID，则是修改操作，否则是新增操作
        if (id) {

            // 获取今天的日期（考虑时区）
            const now = new Date()
            const today = formatDateTime(now).substring(0, 10)

            if ((startTime <= today && endTime >= today) && timeLimit) {
                state = 0
            } else if (today > endTime) {
                state = 3
            }

            // if (state == 3) {
            //     return res.json({
            //         msg: "已结束的考试不能修改！",
            //         code: 400,
            //         success: false
            //     })
            // }

            // 更新考试信息
            await db.query(`
                UPDATE el_exam 
                SET title=?, content=?, open_type=?, state=?, time_limit=?, 
                    total_score=?, total_time=?, qualify_score=?, update_time=NOW(),
                    start_time=?, end_time=?
                WHERE id=?
            `, [title, content, openType, state, timeLimit ? 1 : 0, totalScore, totalTime, qualifyScore, startTime, endTime, id]);

            // 删除原有的题库关联
            await db.query('DELETE FROM el_exam_repo WHERE exam_id=?', [id])

            // 删除原有的部门关联
            await db.query('DELETE FROM el_exam_depart WHERE exam_id=?', [id])


        } else {

            // 新增考试信息
            await db.query(`
                INSERT INTO el_exam (id, title, content, open_type, state, time_limit, 
                    total_score, total_time, qualify_score, create_time, update_time)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())
            `, [newId, title, content, openType, 2, timeLimit ? 1 : 0, totalScore, totalTime, qualifyScore])
        }

        // 添加部门关联
        if (departIds && departIds.length > 0) {
            for (const departId of departIds) {
                await db.query(`INSERT INTO el_exam_depart (id, exam_id, depart_id) VALUES (?, ?, ?)`, [generate19DigitId(), id || newId, departId])
            }
        }

        // 添加题库关联
        for (const repo of repoList) {
            await db.query(`
                INSERT INTO el_exam_repo (id, exam_id, repo_id, radio_count, radio_score, 
                    multi_count, multi_score, judge_count, judge_score)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
            `, [
                Date.now().toString() + Math.random().toString().substr(2, 5),
                id || newId,
                repo.repoId,
                repo.radioCount,
                repo.radioScore,
                repo.multiCount,
                repo.multiScore,
                repo.judgeCount,
                repo.judgeScore
            ]);
        }
        return res.json({ "msg": "请求成功！", "code": 0, "success": true })
    }
    catch (err) {
        console.error('数据库查询失败:', err);
        return res.json({
            msg: "数据库查询失败",
            code: 500,
            success: false
        })
    }
})

//删除考试接口
router.post('/exam/exam/delete', async (req, res) => {
    try {
        const { ids } = req.body
        await db.query('delete from el_exam where id in (?)', [ids])
        return res.json({ "msg": "请求成功！", "code": 0, "success": true })
    }
    catch (err) {
        console.error('数据库查询失败:', err);
        return res.json({
            msg: "数据库查询失败",
            code: 500,
            success: false
        })
    }
})


//修改考试状态接口
router.post('/exam/exam/state', async (req, res) => {
    try {
        const { ids, state } = req.body
        await db.query('update el_exam set state=? where id in (?)', [state, ids])
        return res.json({ "msg": "请求成功！", "code": 0, "success": true })
    }
    catch (err) {
        console.error('数据库查询失败:', err);
        return res.json({
            msg: "数据库查询失败",
            code: 500,
            success: false
        })
    }
})


//获取最近考试
router.post('/exam/exam/recentExam', async (req, res) => {
    try {
        const { size = 10, current = 1 } = req.body
        const { userId } = req.body.params

        const recentExam = await el_paper.elPaperPaging(size, (current - 1) * size, userId)
        const totalResults = await el_paper.findByPaperStateAndUserId(2, userId) // 不限制数量，只为了计数


        return res.json({
            "msg": "操作成功！",
            "code": 0,
            "data": {
                "records": recentExam,
                "total": totalResults.length,
                "size": size,
                "current": current,
                "orders": [],
                "optimizeCountSql": true,
                "hitCount": false,
                "searchCount": true,
                "pages": Math.ceil(totalResults.length / size)
            },
            success: true
        })
    }
    catch (err) {
        console.error('数据库查询失败:', err);
        return res.json({
            msg: "数据库查询失败",
            code: 500,
            success: false
        })
    }
})

module.exports = router